C:\Users\cubes>mysql -u root -p
Enter password:
mysql> use nb158d;
Database changed
mysql> show tables;
+------------------+
| Tables_in_nb158d |
+------------------+
| countries        |
| login_check      |
| myisam           |
| practice         |
| users            |
+------------------+
5 rows in set (0.03 sec)

mysql> select * from countries;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | India        |
|  2 | paakistan    |
|  3 | nepal        |
|  4 | china        |
|  5 | bhutan       |
|  6 | banglaa desh |
|  7 | sri laanka   |
|  8 | afganistan   |
|  9 | myanmar      |
| 10 | tibbet       |
| 11 | iraq         |
| 12 | iran         |
| 13 | jaapan       |
| 14 | thilaand     |
| 15 | thilaand     |
| 16 | thilaand     |
| 17 | aarab        |
| 18 | aarab        |
| 19 | france       |
| 20 | italy        |
| 21 | ireland      |
+----+--------------+
21 rows in set (0.16 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> update `countries` SET `name`
    -> ='ireland123' WHERE
    -> `id`=21;
Query OK, 1 row affected (0.09 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from countries;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | India        |
|  2 | paakistan    |
|  3 | nepal        |
|  4 | china        |
|  5 | bhutan       |
|  6 | banglaa desh |
|  7 | sri laanka   |
|  8 | afganistan   |
|  9 | myanmar      |
| 10 | tibbet       |
| 11 | iraq         |
| 12 | iran         |
| 13 | jaapan       |
| 14 | thilaand     |
| 15 | thilaand     |
| 16 | thilaand     |
| 17 | aarab        |
| 18 | aarab        |
| 19 | france       |
| 20 | italy        |
| 21 | ireland123   |
+----+--------------+
21 rows in set (0.00 sec)

mysql> savepoint test1;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE `countries`
    -> SET `name`='itaaly123'
    -> WHERE `id`='20';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from countries;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | India        |
|  2 | paakistan    |
|  3 | nepal        |
|  4 | china        |
|  5 | bhutan       |
|  6 | banglaa desh |
|  7 | sri laanka   |
|  8 | afganistan   |
|  9 | myanmar      |
| 10 | tibbet       |
| 11 | iraq         |
| 12 | iran         |
| 13 | jaapan       |
| 14 | thilaand     |
| 15 | thilaand     |
| 16 | thilaand     |
| 17 | aarab        |
| 18 | aarab        |
| 19 | france       |
| 20 | itaaly123    |
| 21 | ireland123   |
+----+--------------+
21 rows in set (0.00 sec)

mysql> savepoint test2;
Query OK, 0 rows affected (0.00 sec)

mysql> ROLLBACK to test1;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from countries;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | India        |
|  2 | paakistan    |
|  3 | nepal        |
|  4 | china        |
|  5 | bhutan       |
|  6 | banglaa desh |
|  7 | sri laanka   |
|  8 | afganistan   |
|  9 | myanmar      |
| 10 | tibbet       |
| 11 | iraq         |
| 12 | iran         |
| 13 | jaapan       |
| 14 | thilaand     |
| 15 | thilaand     |
| 16 | thilaand     |
| 17 | aarab        |
| 18 | aarab        |
| 19 | france       |
| 20 | italy        |
| 21 | ireland123   |
+----+--------------+
21 rows in set (0.00 sec)

mysql> ROLLBACK to test2;
ERROR 1305 (42000): SAVEPOINT test2 does not exist
mysql> COMMIT;
Query OK, 0 rows affected (0.01 sec)

mysql>
=================================================================
CREATE TABLE `countries`(`id` INT NOT NULL AUTO_INCREMENT, name VARCHAR(100),code CHAR(2), PRIMARY KEY (`id`) ) ENGINE=INNODB;

CREATE TABLE `users`(`id` INT NOT NULL AUTO_INCREMENT, name VARCHAR(100), country_id INT, PRIMARY KEY (`id`), FOREIGN KEY (`country_id`) REFERENCES countries(id)  ON DELETE CASCADE  ON UPDATE CASCADE ) ENGINE=INNODB;
Query OK, 0 rows affected (0.14 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from users;
+----+---------+------------+
| id | name    | country_id |
+----+---------+------------+
|  1 | vikas   |          1 |
|  2 | prakash |          2 |
|  3 | ravi    |          2 |
|  4 | suresh  |          3 |
+----+---------+------------+
4 rows in set (0.00 sec)

mysql> update users set name=abhi where id=3;
ERROR 1054 (42S22): Unknown column 'abhi' in 'field list'
mysql> update users set name='abhi' where id=3;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> savepoint point1;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into users (name, country_id)values(rajan, 3);
ERROR 1054 (42S22): Unknown column 'rajan' in 'field list'
mysql> insert into users (name, country_id)values('rajan', 3);
Query OK, 1 row affected (0.00 sec)

mysql> select * from users;
+----+---------+------------+
| id | name    | country_id |
+----+---------+------------+
|  1 | vikas   |          1 |
|  2 | prakash |          2 |
|  3 | abhi    |          2 |
|  4 | suresh  |          3 |
|  5 | rajan   |          3 |
+----+---------+------------+
5 rows in set (0.00 sec)

mysql> rollback to point1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from users;
+----+---------+------------+
| id | name    | country_id |
+----+---------+------------+
|  1 | vikas   |          1 |
|  2 | prakash |          2 |
|  3 | abhi    |          2 |
|  4 | suresh  |          3 |
+----+---------+------------+
4 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.04 sec)
